package com.dao;

import com.domain.*;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;


@Repository
public interface Maintenance_orderDao {


    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state " +
            "where maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id")
    public List<Maintenance_order> findAllOrder();

    /**
     * 根据部门查询所有维修单
     * @param department_id
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state " +
            "where maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id and " +
            "maintenance_order.department_id = #{department_id}")
    public List<Maintenance_order> findAllOrderByDepartment(Integer department_id);

    /**
     * 添加维修单并返回维修单id
     * @param maintenance_order
     */
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    @Insert("insert into maintenance_order (order_name, equipment_id, problem_image, problem_video, problem_word, department_id, maintenance_location, maintenance_way_id, orderPeriodOfValidity) " +
            "values(#{order_name}, #{equipment_id}, #{problem_image}, #{problem_video}, #{problem_word}, #{department_id}, #{maintenance_location}, #{maintenance_way_id}, #{orderPeriodOfValidity})")
    public void addOrder(Maintenance_order maintenance_order);

    /**
     * 添加维修流程
     * @param maintenance_process
     */
    @Insert("insert into maintenance_process (order_id, task_id, transactor_id, finished_time, detail, reason) " +
            "values(#{order_id}, #{task_id}, #{transactor_id}, #{finished_time}, #{detail}, #{reason})")
    public void addProcess(Maintenance_process maintenance_process);

    /**
     * 通过维修方式id查询名称
     * @param way_id
     * @return
     */
    @Select("select maintenance_way.maintenance_way_name from maintenance_way where maintenance_way.maintenance_way_id = #{way_id}")
    public String findWay(Integer way_id);

    /**
     * 添加耗材清单
     * @param expense_details
     */
    @Insert("insert into expense_details (order_id, consumable_name, consumable_price, consumable_num, unit_id, classifier) " +
            "values(#{order_id}, #{consumable_name}, #{consumable_price}, #{consumable_num}, #{unit_id}, #{classifier})")
    public void addExpense(Expense_details expense_details);


    /**
     * 添加评价
     * @param evaluation
     */
    @Insert("insert into evaluation (order_id, maintenance_time, maintenance_qualification, maintenance_attitude, detail) " +
            "values(#{order_id}, #{maintenance_time}, #{maintenance_qualification}, #{maintenance_attitude}, #{detail})")
    public void addEvaluation(Evaluation evaluation);

    /**
     * 添加追评
     * @param additional_evaluation
     */
    @Insert("insert into additional_evaluation (order_id, additional_evaluation) " +
            "values(#{order_id}, #{additional_evaluation})")
    public void addAdditionalEvaluation(Additional_evaluation additional_evaluation);


    /**
     * 维修单添加维修要求
     * @param order_id
     * @param user_id
     * @param request
     */
    @Insert("insert into maintenance_request (order_id, user_id, maintenance_request) values(#{order_id}, #{user_id}, #{request})")
    public void addRequest(@Param("order_id") Integer order_id, @Param("user_id") Integer user_id, @Param("request") String request);

    /**
     * 维修单修改维修要求
     * @param order_id
     * @param user_id
     * @param request
     */
    @Update("update maintenance_request set maintenance_request = #{request} where order_id = #{order_id} and user_id = ${user_id}")
    public void updateRequest(@Param("order_id") Integer order_id, @Param("user_id") Integer user_id, @Param("request") String request);


    /**
     * 根据维修单名称模糊查询
     * @param order_name
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state " +
            "where maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id and " +
            "instr(maintenance_order.order_name,#{order_name}) > 0")
    public List<Maintenance_order> findOrderByName(String order_name);


    /**
     * 根据维修单名称和单位模糊查询
     * @param order_name
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from (select maintenance_order.* from user_insiderunit, maintenance_order, maintenance_process, user " +
            "where maintenance_order.id = maintenance_process.order_id and user.id = maintenance_process.transactor_id and " +
            "maintenance_process.task_id = 1 and user.id = user_insiderunit.user_id and " +
            "user_insiderunit.insiderUnit_id = #{insiderUnit_id}) as maintenance_order, equipment, order_state " +
            "where maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id and " +
            "instr(maintenance_order.order_name,#{order_name}) > 0")
    public List<Maintenance_order> findInsiderUnitOrderByName(@Param("insiderUnit_id") Integer insiderUnit_id,
                                                              @Param("order_name") String order_name);



    /**
     * 根据维修单名称和部门模糊查询
     * @param order_name
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state " +
            "where maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id and " +
            "instr(maintenance_order.order_name,#{order_name}) > 0 and " +
            "maintenance_order.department_id = #{department_id}")
    public List<Maintenance_order> findDepartmentOrderByName(@Param("department_id") Integer department_id,
                                                              @Param("order_name") String order_name);

    /**
     * 修改维修单
     * @param maintenance_order
     */
    @Update("update maintenance_order set order_name = #{order_name}, final_price = #{final_price}, unit_id = #{unit_id}, solution_image = #{solution_image}, " +
            "solution_video = #{solution_video}, solution_word = #{solution_word}, warranty_period = #{warranty_period}, order_state = #{order_state}, " +
            "second_price = #{second_price}, expense_file = #{expense_file}, biddingPeriodOfValidity = #{biddingPeriodOfValidity}, estimate_price = #{estimate_price} " +
            "where id = #{id}")
    public void updateOrder(Maintenance_order maintenance_order);


    /**
     * 查找一个维修单所有的维修要求
     * @param order_id
     * @return
     */
    @Select("select maintenance_request.*, user.name " +
            "from maintenance_request, user " +
            "where maintenance_request.user_id = user.id and " +
            "maintenance_request.order_id = #{order_id}")
    public List<Maintenance_request> findRequestByOrderId(@Param("order_id") Integer order_id);


    /**
     * 查找维修单中一个用户的维修要求
     * @param order_id
     * @param user_id
     * @return
     */
    @Select("select maintenance_request.* from maintenance_request " +
            "where maintenance_request.order_id = #{order_id} and maintenance_request.user_id = #{user_id}")
    public Maintenance_request findRequest(@Param("order_id") Integer order_id, @Param("user_id") Integer user_id);

    /**
     * 删除一个维修单所有的要求
     * @param order_id
     */
    @Delete("delete from maintenance_request where order_id = #{order_id}")
    public void delRequest(Integer order_id);

    /**
     * 查找一个维修单
     * @param id
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state " +
            "where maintenance_order.id = #{id} and maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id")
    public Maintenance_order findOneById(Integer id);


    /**
     * 查找一个维修单的流程
     * @param id
     * @return
     */
    @Select("select maintenance_process.*, task.task_name, user.name " +
            "from maintenance_process, task, user, maintenance_order " +
            "where maintenance_process.order_id =#{id} and " +
            "maintenance_process.order_id = maintenance_order.id and maintenance_process.task_id = task.task_id and maintenance_process.transactor_id = user.id")
    public List<Maintenance_process> findOrderProcess(Integer id);

    /**
     * 查找一个维修单的耗材清单
     * @param id
     * @return
     */
    @Select("select expense_details.* from expense_details, maintenance_order where expense_details.order_id = #{id} and " +
            "expense_details.order_id = maintenance_order.id and expense_details.unit_id = #{unit_id}")
    public List<Expense_details> findExpenseDetails(@Param("id") Integer id, @Param("unit_id") Integer unit_id);


    /**
     * 查找一个维修单的评价表
     * @param id
     * @return
     */
    @Select("select evaluation.* from evaluation, maintenance_order " +
            "where evaluation.order_id = maintenance_order.id and " +
            "evaluation.order_id = #{id}")
    public Evaluation findEvaluation(Integer id);

    /**
     * 查找一个维修单的追评表
     * @param id
     * @return
     */
    @Select("select additional_evaluation.* from additional_evaluation, maintenance_order " +
            "where additional_evaluation.order_id = maintenance_order.id and " +
            "additional_evaluation.order_id = #{id}")
    public List<Additional_evaluation> findAdditionalEvaluation(Integer id);

    /**
     * 根据部门查找某年某月所有维修单
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, maintenance_process, equipment, order_state " +
            "where maintenance_order.id = maintenance_process.order_id and " +
            "date_format(maintenance_process.finished_time,'%Y-%m')=#{time} and " +
            "maintenance_process.task_id = 1 and maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.department_id = #{department_id} and " +
            "maintenance_order.order_state = order_state.state_id")
    public List<Maintenance_order> findAll(@Param("time") String time,
                                           @Param("department_id") Integer department_id);


    /**
     * 删除流程
     * @param order_id
     */
    @Delete("delete from maintenance_process where order_id = #{order_id}")
    public void delProcess(Integer order_id);

    /**
     * 删除维修单
     * @param order_id
     */
    @Delete("delete from maintenance_order where id = #{order_id}")
    public void delOrder(Integer order_id);

    /**
     * 删除追评
     * @param order_id
     */
    @Delete("delete from additional_evaluation where order_id = #{order_id}")
    public void delAdditionalEvaluation(Integer order_id);

    /**
     * 删除评价
     * @param order_id
     */
    @Delete("delete from evaluation where order_id = #{order_id}")
    public void delEvaluation(Integer order_id);

    /**
     * 删除维修清单
     * @param order_id
     */
    @Delete("delete from expense_details where order_id = #{order_id}")
    public void delExpenseDetails(Integer order_id);

    /**
     * 根据维修方删除维修清单
     * @param order_id
     */
    @Delete("delete from expense_details where order_id = #{order_id} and unit_id = #{unit_id}")
    public void delExpenseDetailsByUnit(@Param("order_id") Integer order_id,@Param("unit_id") Integer unit_id);


    /**
     * 删除竞单信息
     * @param order_id
     */
    @Delete("delete from bidding where order_id = #{order_id}")
    public void delBidding(Integer order_id);


    /**
     * 根据部门查找某年所有维修单
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name from " +
            "maintenance_order, " +
            "( SELECT maintenance_process.* FROM maintenance_process WHERE date_format(maintenance_process.finished_time,'%Y') = '${year}' ) as maintenance_process, " +
            "equipment, order_state " +
            "where maintenance_order.id = maintenance_process.order_id and " +
            "maintenance_process.task_id = 1 and maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.department_id = #{department_id} and " +
            "maintenance_order.order_state = order_state.state_id")
    public List<Maintenance_order> findAllYear(@Param("year") Integer year,
                                               @Param("department_id") Integer department_id);

    /**
     * 查找某年所有维修单
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name from " +
            "maintenance_order, " +
            "( SELECT maintenance_process.* FROM maintenance_process WHERE date_format(maintenance_process.finished_time,'%Y') = '${year}' ) as maintenance_process, " +
            "equipment, order_state " +
            "where maintenance_order.id = maintenance_process.order_id and " +
            "maintenance_process.task_id = 1 and maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id")
    public List<Maintenance_order> findAllOrderByYear(@Param("year") Integer year);


    /**
     * 查找某年某月所有维修单
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, maintenance_process, equipment, order_state " +
            "where maintenance_order.id = maintenance_process.order_id and " +
            "date_format(maintenance_process.finished_time,'%Y-%m')=#{time} and " +
            "maintenance_process.task_id = 1 and maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id")
    public List<Maintenance_order> findAllOrderByTime(@Param("time") String time);



    /**
     * 查找某年某月所有已完成的维修单
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, maintenance_process, equipment, order_state " +
            "where maintenance_order.id = maintenance_process.order_id and " +
            "date_format(maintenance_process.finished_time,'%Y-%m')=#{time} and " +
            "maintenance_process.task_id = 1 and maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id and " +
            "(maintenance_order.order_state = 8 or maintenance_order.order_state = 9 or maintenance_order.order_state = 10)")
    public List<Maintenance_order> findAllOrderByTimeFinished(@Param("time") String time);

    /**
     * 查找某年某月所有未完成的维修单
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, maintenance_process, equipment, order_state " +
            "where maintenance_order.id = maintenance_process.order_id and " +
            "date_format(maintenance_process.finished_time,'%Y-%m')=#{time} and " +
            "maintenance_process.task_id = 1 and maintenance_order.equipment_id = equipment.equipment_id and " +
            "maintenance_order.order_state = order_state.state_id and " +
            "(maintenance_order.order_state != 8 and maintenance_order.order_state != 9 and maintenance_order.order_state != 10 and maintenance_order.order_state != 11 and maintenance_order.order_state != 15)")
    public List<Maintenance_order> findAllOrderByTimeUnFinished(@Param("time") String time);

    /**
     * 根据用户查询所有维修单
     * @param user_id
     * @return
     */
    @Select("select maintenance_order.* from " +
            "maintenance_order, " +
            "( SELECT maintenance_process.* FROM maintenance_process WHERE maintenance_process.transactor_id = #{user_id} ) as maintenance_process " +
            "where maintenance_order.id = maintenance_process.order_id")
    public List<Maintenance_order> findAllOrderByUser(Integer user_id);
    /**
     * 根据维修单和公司id查找预估金额
     * @param order_id
     * @param company_id
     * @return
     */
    @Select("select estimated_price from bidding where order_id = #{id} and company_id = #{company_id}")
    public Float findPrice(@Param("order_id") Integer order_id, @Param("company_id") Integer company_id);

    /**
     * 根据维修单状态查询维修单
     * @param order_state_id
     * @return
     */
    @Select("select maintenance_order.*, equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state " +
            "where maintenance_order.order_state = #{order_state_id} and " +
            "maintenance_order.equipment_id = equipment.equipment_id and " +
            "order_state.state_id = maintenance_order.order_state")
    public List<Maintenance_order> findByState(Integer order_state_id);


    /**
     * 根据用户id按年月查询历史维修单
     * @param user_id
     * @return
     */

    @Select("SELECT maintenance_order.*, order_state.state_name, equipment.equipment_name " +
            "FROM maintenance_order,order_state, equipment, " +
            "( SELECT maintenance_process.* FROM maintenance_process WHERE date_format(maintenance_process.finished_time,'%Y-%m') = #{time} ) as maintenance_process " +
            "WHERE maintenance_order.order_state = order_state.state_id " +
            "AND (maintenance_order.order_state = 8 OR maintenance_order.order_state = 9 OR maintenance_order.order_state = 10 OR maintenance_order.order_state = 11 OR maintenance_order.order_state = 15) " +
            "AND maintenance_process.transactor_id = #{user_id} and maintenance_process.order_id = maintenance_order.id " +
            "AND maintenance_order.equipment_id = equipment.equipment_id")
    public List<Maintenance_order> findHistoryByUser(@Param("time") String time,
                                                     @Param("user_id") Integer user_id);

    /**
     * 根据用户id按年查询历史维修单
     * @param user_id
     * @return
     */
    @Select("SELECT maintenance_order.*, order_state.state_name,  equipment.equipment_name " +
            "FROM maintenance_order,order_state,equipment," +
            "( SELECT maintenance_process.* FROM maintenance_process WHERE date_format(maintenance_process.finished_time,'%Y') = '${year}' ) as maintenance_process " +
            "WHERE maintenance_order.order_state = order_state.state_id " +
            "AND (maintenance_order.order_state = 8 OR maintenance_order.order_state = 9 OR maintenance_order.order_state = 10 OR maintenance_order.order_state = 11 OR maintenance_order.order_state = 15) " +
            "AND maintenance_process.transactor_id = #{user_id} and maintenance_process.order_id = maintenance_order.id " +
            "AND maintenance_order.equipment_id = equipment.equipment_id")
    public List<Maintenance_order> findHistoryByUserYear(@Param("year")Integer year,
                                                         @Param("user_id")Integer user_id);

    /**
     * 根据用户id查询正在进行的维修单
     * @param user_id
     * @return
     */
     @Select("select maintenance_order.*, order_state.state_name from maintenance_order, order_state, maintenance_process " +
             "where maintenance_order.order_state = order_state.state_id and " +
             "(maintenance_order.order_state != 8 and maintenance_order.order_state != 9 and maintenance_order.order_state != 10 and maintenance_order.order_state != 11 and maintenance_order.order_state != 15) and " +
             "maintenance_process.transactor_id = #{user_id} and maintenance_process.order_id = maintenance_order.id")
    public List<Maintenance_order> findOrderByUser(Integer user_id);

    /**
     * 根据维修方id查询正在进行的维修单
     * @param user_id
     * @return
     */
    @Select("select maintenance_order.*, order_state.state_name from maintenance_order, order_state " +
            "where maintenance_order.order_state = order_state.state_id and " +
            "(maintenance_order.order_state != 8 and maintenance_order.order_state != 9 and maintenance_order.order_state != 10 and maintenance_order.order_state != 11 and maintenance_order.order_state != 15) and " +
            "maintenance_order.unit_id = #{user_id}")
    public List<Maintenance_order> findOrderByUnit(Integer user_id);

    /**
     * 根据维修方id查询已完成的维修单
     * @param user_id
     * @return
     */
    @Select("select maintenance_order.*, order_state.state_name from maintenance_order, order_state " +
            "where maintenance_order.order_state = order_state.state_id and " +
            "(maintenance_order.order_state == 8 or maintenance_order.order_state == 9 or maintenance_order.order_state == 10 or maintenance_order.order_state == 11 or maintenance_order.order_state == 15) and " +
            "maintenance_order.unit_id = #{user_id}")
    public List<Maintenance_order> findHOrderByUnit(Integer user_id);

    /**
     * 根据设备id查询所有维修单
     * @param equipment_id
     * @return
     */
    @Select("select maintenance_order.*,equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state where " +
            "maintenance_order.equipment_id = #{equipment_id} and " +
            "maintenance_order.equipment_id = equipment.equipment_id and maintenance_order.order_state = order_state.state_id")
    public List<Maintenance_order> findHOrderByEquipment(Integer equipment_id);

    /**
     * 根据设备id查询正在进行的维修单
     * @param equipment_id
     * @return
     */
    @Select("select maintenance_order.*,equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state where " +
            "maintenance_order.equipment_id = #{equipment_id} and " +
            "(maintenance_order.order_state != 8 and maintenance_order.order_state != 9 and maintenance_order.order_state != 10 and maintenance_order.order_state != 11 and maintenance_order.order_state != 15) " +
            "and maintenance_order.equipment_id = equipment.equipment_id and maintenance_order.order_state = order_state.state_id")
    public List<Maintenance_order> findOrderByEquipment(Integer equipment_id);


    /**
     * 根据维修类型查询维修单
     * @param maintenance_type
     * @return
     */
    @Select("select maintenance_order.*,equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state " +
            "where maintenance_order.equipment_id = equipment.equipment_id and " +
            "equipment.equipment_type = #{maintenance_type} and " +
            "maintenance_order.order_state = order_state.state_id")
    public List<Maintenance_order> findOrderByType(Integer maintenance_type);


    /**
     * 查询所有正在进行的维修单
     * @return
     */
    @Select("select maintenance_order.*,equipment.equipment_name, order_state.state_name " +
            "from maintenance_order, equipment, order_state where " +
            "(maintenance_order.order_state != 8 and maintenance_order.order_state != 9 and maintenance_order.order_state != 10 and maintenance_order.order_state != 11 and maintenance_order.order_state != 15) " +
            "and maintenance_order.equipment_id = equipment.equipment_id and maintenance_order.order_state = order_state.state_id")
    public List<Maintenance_order> findAllUnfinishedOrder();

}
